﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TIS.DAO
{
    public class NewsDAO
    {
        private static readonly String Url = ConfigurationManager.AppSettings["Main.ConnectionString"];
        public NewsDAO()
        {

        }

        private SqlConnection _connection;
        private SqlConnection GetConnection()
        {
            _connection = new SqlConnection(Url);
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            return _connection;
        }
        public DataTable GetService(int categoryId,int languageId)
        {
            try
            {

                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@CategoryId", categoryId);
                spr[1] = new SqlParameter("@LanguageId", languageId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 4 * FROM News WHERE NewsStatus = 1 AND CategoryId = @CategoryId AND LanguageId = @LanguageId ORDER BY NewsDate DESC", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }
        public DataTable GetSanPham()
        {
            try
            {
                _connection = GetConnection();
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 4 * FROM News WHERE NewsStatus = 1 AND CategoryId = 1 ORDER BY NewsDate DESC");
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetDichVu()
        {
            try
            {
                _connection = GetConnection();
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 4 * FROM News WHERE NewsStatus = 1 AND CategoryId = 2 ORDER BY NewsDate DESC");
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetGiaiPhap()
        {
            try
            {
                _connection = GetConnection();
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 4 * FROM News WHERE NewsStatus = 1 AND CategoryId = 3 ORDER BY NewsDate DESC");
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetTinHoatDong(int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
              
                spr[0] = new SqlParameter("@LanguageId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 2 * FROM News WHERE NewsStatus = 1 AND CategoryId = 5 AND LanguageId = @LanguageId ORDER BY NewsDate DESC", spr);
                return ds.Tables[0];
            }
            finally { _connection.Close(); }
        }

        public DataTable GetTinKhac(int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@LanguageId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 4 * FROM News WHERE NewsStatus = 1 AND CategoryId = 5 AND  LanguageId = @LanguageId AND NewsId NOT IN(SELECT TOP 2 NewsId FROM News WHERE NewsStatus = 1 AND CategoryId = 5 ORDER BY NewsDate DESC) ORDER BY NewsDate DESC",spr);
                return ds.Tables[0];
            }
            finally { _connection.Close(); }
        }


        public DataTable GetContent(int id,int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@NewsId", id);
                spr[1] = new SqlParameter("@LanguageId",langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 1 News.*,Category.CategoryImage,Category.CategoryName,Category.CategorySEOUrl FROM News JOIN Category ON News.CategoryId = Category.CategoryId WHERE News.NewsId = @NewsId AND News.LanguageId = @LanguageId", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetListNews(int id, int page, int num)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[3];
                spr[0] = new SqlParameter("@CategoryId", id);
                spr[1] = new SqlParameter("@PageSize", page);
                spr[2] = new SqlParameter("@PageNum", num);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.StoredProcedure, "SELECT_NEWS_BY_CATEGORY", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetNewsRight(int id,int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@CategoryId", id);
                spr[1] = new SqlParameter("@LanguageId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 5 * FROM News WHERE CategoryId = @CategoryId AND LanguageId = @LanguageId AND NewsStatus = 1 ORDER BY NewsDate DESC", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetTinKhacTisNews(int categoryId, int newsId, int languageId,string date)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[4];
                spr[0] = new SqlParameter("@CategoryId", categoryId);
                spr[1] = new SqlParameter("@NewsId", newsId);
                spr[2] = new SqlParameter("@LanguageId", languageId);
                spr[3] = new SqlParameter("@newsDate", date);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 5 * FROM News WHERE NewsStatus = 1 AND LanguageId = @LanguageId AND CategoryId = @CategoryId AND NewsDate <= DATEADD(dd,0,@newsDate) AND NewsId NOT IN(@NewsId) ORDER BY NewsDate DESC", spr);
                return ds.Tables[0];
            }
            finally { _connection.Close(); }
        }

        public DataTable GetNewsAll(int categoryId,int langId)
        {
            try
            {
                _connection = GetConnection();
                if (categoryId != 0)
                {

                    SqlParameter[] spr = new SqlParameter[2];
                    spr[0] = new SqlParameter("@LanguageId", langId);
                    spr[1] = new SqlParameter("@CategoryId", categoryId);
                    DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT News.*,Category.CategoryName FROM News JOIN Category ON News.CategoryId = Category.CategoryId WHERE News.LanguageId = @LanguageId AND News.CategoryId = @CategoryId", spr);
                    return ds.Tables[0];
                }
                else
                {
                    SqlParameter[] spr = new SqlParameter[1];
                    spr[0] = new SqlParameter("@LanguageId", langId);
                    DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT News.*,Category.CategoryName FROM News JOIN Category ON News.CategoryId = Category.CategoryId WHERE News.LanguageId = @LanguageId", spr);
                    return ds.Tables[0];
                }

            }
            finally
            {
                _connection.Close();
            }
        }

        public void InsertNews(string title,int categoryId,string filePath,string content,
            string desc, string metaTitle,string meteDesc,string metaKeyword,string seoUrl,int status, int languageId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[11];
                spr[0] = new SqlParameter("@CategoryId", categoryId);
                spr[1] = new SqlParameter("@NewsTitle", title);
                spr[2] = new SqlParameter("@NewsContent", content);
                spr[3] = new SqlParameter("@NewsImages", filePath);
                spr[4] = new SqlParameter("@NewsDescription", desc);
                spr[5] = new SqlParameter("@NewsMetaTitle", metaTitle);
                spr[6] = new SqlParameter("@NewsMetaDescription", meteDesc);
                spr[7] = new SqlParameter("@NewsMetaKeyword", metaKeyword);
                spr[8] = new SqlParameter("@NewsSEOUrl", seoUrl);
                spr[9] = new SqlParameter("@LanguageId", languageId);
                spr[10] = new SqlParameter("@NewsStatus", status);

                SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                    "INSERT News(CategoryId,NewsTitle,NewsContent,NewsImages,NewsDescription,NewsMetaTitle,NewsMetaDescription,NewsMetaKeyword,NewsSEOUrl,LanguageId,NewsStatus) "+
                    "VALUES(@CategoryId,@NewsTitle,@NewsContent,@NewsImages,@NewsDescription,@NewsMetaTitle,@NewsMetaDescription,@NewsMetaKeyword,@NewsSEOUrl,@LanguageId,@NewsStatus) ", spr);
            }
            finally { _connection.Close(); }
        }

        public DataTable GetNewsById(int id)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@NewsId", id);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "select * from [News] WHERE NewsId = @NewsId", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public void DeleteNews(int id)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@NewsId", id);
                SqlHelper.ExecuteNonQuery(_connection, CommandType.Text, "DELETE FROM [News] WHERE NewsId = @NewsId", spr);

            }
            finally
            {
                _connection.Close();
            }
        }

        public void UpdateNews(int newsId,string title, int categoryId, string filePath, string content,
            string desc, string metaTitle, string meteDesc, string metaKeyword, string seoUrl, int status, int languageId,int check)
        {
            try
            {
                if (filePath != "")
                {
                    _connection = GetConnection();
                    SqlParameter[] spr = new SqlParameter[12];
                    spr[0] = new SqlParameter("@CategoryId", categoryId);
                    spr[1] = new SqlParameter("@NewsTitle", title);
                    spr[2] = new SqlParameter("@NewsContent", content);
                    spr[3] = new SqlParameter("@NewsImages", filePath);
                    spr[4] = new SqlParameter("@NewsDescription", desc);
                    spr[5] = new SqlParameter("@NewsMetaTitle", metaTitle);
                    spr[6] = new SqlParameter("@NewsMetaDescription", meteDesc);
                    spr[7] = new SqlParameter("@NewsMetaKeyword", metaKeyword);
                    spr[8] = new SqlParameter("@NewsSEOUrl", seoUrl);
                    spr[9] = new SqlParameter("@LanguageId", languageId);
                    spr[10] = new SqlParameter("@NewsStatus", status);
                    spr[11] = new SqlParameter("@NewsId", newsId);
                    SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                        "UPDATE News SET CategoryId = @CategoryId,NewsTitle = @NewsTitle,NewsContent = @NewsContent," + (check == 1 ? "NewsDate = getDate()," : "") + "NewsImages = @NewsImages,NewsDescription = @NewsDescription,NewsMetaTitle = @NewsMetaTitle,NewsMetaDescription = @NewsMetaDescription," +
                        "NewsMetaKeyword = @NewsMetaKeyword,NewsSEOUrl = @NewsSEOUrl,LanguageId = @LanguageId,NewsStatus = @NewsStatus WHERE NewsId = @NewsId", spr);
                }
                else
                {
                    _connection = GetConnection();
                    SqlParameter[] spr = new SqlParameter[11];
                    spr[0] = new SqlParameter("@CategoryId", categoryId);
                    spr[1] = new SqlParameter("@NewsTitle", title);
                    spr[2] = new SqlParameter("@NewsContent", content);
                    spr[3] = new SqlParameter("@NewsDescription", desc);
                    spr[4] = new SqlParameter("@NewsMetaTitle", metaTitle);
                    spr[5] = new SqlParameter("@NewsMetaDescription", meteDesc);
                    spr[6] = new SqlParameter("@NewsMetaKeyword", metaKeyword);
                    spr[7] = new SqlParameter("@NewsSEOUrl", seoUrl);
                    spr[8] = new SqlParameter("@LanguageId", languageId);
                    spr[9] = new SqlParameter("@NewsStatus", status);
                    spr[10] = new SqlParameter("@NewsId", newsId);
                    SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                        "UPDATE News SET CategoryId = @CategoryId,NewsTitle = @NewsTitle,NewsContent = @NewsContent,"+(check == 1 ? "NewsDate = getDate()," : "")+"NewsDescription = @NewsDescription,NewsMetaTitle = @NewsMetaTitle,NewsMetaDescription = @NewsMetaDescription," +
                        "NewsMetaKeyword = @NewsMetaKeyword,NewsSEOUrl = @NewsSEOUrl,LanguageId = @LanguageId,NewsStatus = @NewsStatus WHERE NewsId = @NewsId", spr);
                }
            }
            finally { _connection.Close(); }
        }

        public DataTable GetNewsByCatId(int catId, int langId)
        {
            try
            {
                _connection = GetConnection();
                var spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@LanguageId", langId);
                spr[1] = new SqlParameter("@CategoryId",catId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM [News] WHERE CategoryId = @CategoryId AND LanguageId = @LanguageId ", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }
    }
}